rdoParameter Object, rdoParameters Collection, Direction Property Example

This example executes a stored procedure against the SQL Server 'Pubs’ database. The procedure text is also included here so you can setup this example on your own machine. The stored procedure expects your code to provide three input arguments: A string to use in an expression to choose the title, and two numbers used to choose a price range for the books. The procedure returns the number of books that fall in the range, and the maximum price of the books. It also returns a set of rows containing detailed information about the books.

To establish the connection, we assume the name of the server is "SEQUEL" and it is a Microsoft SQL Server – this is a DSN-less connection. Next, we use the ODBC CALL syntax to prepare the query. Notice that each parameter is marked with a question mark. Once, marked, the rdoParameters collection is used to set the direction for the output and return value parameters and the initial values for the input parameters. While you don't see the rdoParameters collection called out specifically, understand that it is the default collection of the rdoQuery object so references are made simpler by not including a reference to the rdoParameters collection itself.

Sub RunQuery_Click()
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim qd As New rdoQuery
Dim cl As rdoColumn
Const None As String = ""

cn.Connect = "uid=;pwd=;server=SEQUEL;" _
    & "driver={SQL Server};database=pubs;" _
    & "DSN='';"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt

Set qd.ActiveConnection = cn
qd.SQL = "{ ? = Call ShowOutputRS (?,?,?,?,?) }"
qd(0).Direction = rdParamReturnValue
qd(4).Direction = rdParamOutput
qd(5).Direction = rdParamOutput
qd(1) = "c"
qd(2) = 5
qd(3) = 50

Set rs = qd.OpenResultset(rdOpenForwardOnly, _
    rdConcurReadOnly)

For Each cl In rs.rdoColumns
    Debug.Print cl.Name,
Next
Debug.Print

Do Until rs.EOF
    For Each cl In rs.rdoColumns
        Debug.Print cl.Value,
    Next
    rs.MoveNext
Debug.Print
Loop

Debug.Print "Output from SP="; qd(3)
Debug.Print "Return Status from SP="; qd(0)

rs.Close
qd.Close
cn.Close

End Sub

This is the stored procedure that is executed by the example shown above.

CREATE PROCEDURE ShowOutputRS 
(
   @Ser varChar(128),
   @PriceLow Integer,
   @PriceHigh Integer,
   @Hits Integer OUTPUT,
   @MaxPrice integer OUTPUT
)
AS
Select @MaxPrice = Max(Price) from Titles
where Charindex(@Ser, title) > 0 
and price between @priceLow and @priceHigh

Select * from Titles
where Charindex(@Ser, title) > 0 
and price between @priceLow and @PriceHigh

Select @Hits = @@RowCount

return @@ROWCOUNT